using System;
using System.Collections.Generic;
using System.Text;
using DTOLayer;
using System.Collections;
using System.Data.OleDb;
using System.Data;

namespace BUSLayer
{
    public class PhieuGoiTienDAO
    {
        public static void InsertPhieuGoiTien(PhieuGoiTienDTO pg)
        {
            //B1&B2 Tao chuoi ket noi va ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = Database.ConnectionData();
            //B3 Tao chuoi strSQL ket noi CSDL
            string strSQL = "";

            strSQL = "Insert Into PHIEUGOITIEN(MaPhieuGoiTien,MaSo,SoTienGoi,NgayGoi) Values(?,?,?,?)";
            //B4 Thuc Thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);

            cmd.Parameters.Add("@MaPhieuGoiTien", OleDbType.LongVarChar);
            cmd.Parameters.Add("@MaSo", OleDbType.LongVarChar);
            cmd.Parameters.Add("@SoTienGoi", OleDbType.Double);
            cmd.Parameters.Add("@NgayGoi", OleDbType.Date);

            cmd.Parameters[0].Value = pg.MaPhieuGoi;
            cmd.Parameters[1].Value = pg.MaSo;
            cmd.Parameters[2].Value = pg.SoTienGoi;
            cmd.Parameters[3].Value = pg.NgayGoi;

            cmd.ExecuteNonQuery();

            //B5Dong ket noi
            cn.Close();
        }

        public static long MaxMaPhieuGoiTien()
        {
            //B1&B2 tao chuoi kiet noi
            // OleDbConnection cn;
            // cn = Database.ConnectionData();
            //B3 Tao chuoi ket noi CSDL
            string sql = "SELECT  Max(MaPhieuGoiTien) From PHIEUGOITIEN";
            //B4 thuc thi chuoi sql
            // OleDbCommand cmd = new OleDbCommand(sql, cn);
            long kq;
            // kq= long.Parse( cmd.ExecuteScalar().ToString());
            //cn.Close();

            kq = Database.ExecuteScalar(sql);
            return kq;

        }

        public static void UpdatePhieuGoiTien(PhieuGoiTienDTO pg)
        {
            
            //B1&B2 Tao chuoi ket noi va mo ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = Database.ConnectionData();
            //B3 Tao chuoi strSQL thao tac CSDL
            string strSQL;

            strSQL = "UPDATE PHIEUGOITIEN Set MaSo=? ,SoTienGoi=?,NgayGoi=? Where MaPhieuGoiTien=?";
         

            //B4 Thuc Thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);

            cmd.Parameters.Add("@MaSo", OleDbType.LongVarChar);
            cmd.Parameters.Add("@SoTienGoi", OleDbType.Double);
            cmd.Parameters.Add("@NgayGoi", OleDbType.Date);
            cmd.Parameters.Add("MaPhieuGoiTien",OleDbType.LongVarChar);


            cmd.Parameters["@MaSo"].Value=pg.MaSo;
            cmd.Parameters["@SoTienGoi"].Value=pg.SoTienGoi;
            cmd.Parameters["@NgayGoi"].Value=pg.NgayGoi;
            cmd.Parameters["MaPhieuGoiTien"].Value=pg.MaPhieuGoi;

            cmd.ExecuteNonQuery();
            //B5 Dong ket noi
            cn.Close();

        }
        public static void DeletePhieuGoiTienTheoMaPhieuGoi(long mpg)
        {
            //B1 &B2 Tao chuoi ket noi va mo doi tuong ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = Database.ConnectionData();
            //B2 Tao chuoi strSQL Thao Tac CSDL
            string strSQl = "";
            strSQl = "Delete From PHIEUGOITIEN Where MaPhieuGoiTien= ?";
            //B4 Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQl, cn);

            cmd.Parameters.Add("@MaPhieuGoiTien", OleDbType.LongVarChar);
            cmd.Parameters["@MaPhieuGoiTien"].Value = mpg;
            cmd.ExecuteNonQuery();

            //B5 dong ket noi
            cn.Close();
        }
        public static double TongThu(DateTime ngaybaocao, long maso)
        {
            double kq = 0;
           
            //B1&B2 tao chuoi kiet noi
            OleDbConnection cn;
            cn = Database.ConnectionData();
            //B3 Tao chuoi ket noi CSDL
            string strSQL = "Select Sum(SoTienGoi) From PHIEUGOITIEN Where MaSo=? and NgayGoi=?";
            //B4 thuc thi chuoi sql
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaSo",OleDbType.LongVarChar);
            cmd.Parameters.Add("@NgayGoi",OleDbType.Date);

            cmd.Parameters["@MaSo"].Value = maso;
            cmd.Parameters["@NgayGoi"].Value = ngaybaocao;
           //string chu= cmd.ExecuteScalar().ToString();
            kq = double.Parse(cmd.ExecuteScalar().ToString());
            cn.Close();
            return kq;

           
        }

        public static List<PhieuGoiTienDTO> TinhTongThuTheoMaSoVaNgayThu(DateTime ng)
        {
           
          List<PhieuGoiTienDTO> list = new List<PhieuGoiTienDTO>();
            //B1&B2 Tao chuoi ket noi va mo ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = Database.ConnectionData();
            //B3 Tao chuoi strSQL thao tac CSDL
            string strSQL  =@"SELECT MaPhieuGoiTien, MaSo,Sum(SoTienGoi) AS TongTienGoi,NgayGoi
                        FROM PHIEUGOITIEN
                        GROUP BY MaPhieuGoiTien, MaSo, NgayGoi
                        HAVING NgayGoi=#"+ng.ToString()+"#";

            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            //cmd.Parameters.Add("@NgayGoi",OleDbType.Date);
            //cmd.Parameters["@NgayGoi"].Value=ng;

            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                PhieuGoiTienDTO pg = new PhieuGoiTienDTO();
                pg.MaPhieuGoi = long.Parse(dr["MaPhieuGoiTien"].ToString());
                pg.SoTienGoi = double.Parse(dr["TongTienGoi"].ToString());
                pg.MaSo = long.Parse(dr["MaSo"].ToString());
                pg.NgayGoi = DateTime.Parse(dr["NgayGoi"].ToString());

               
                list.Add(pg);

            }
            cn.Close();
            return list;


        }
    }
}
